﻿using System;
using System.Data;
using System.Reflection;
using MySqlConnector;

public class DbHelper
{
    private static string _connectionString;

    static DbHelper()
    {
        ConfigurationBuilder configuration = new ConfigurationBuilder(); // 读取配置文件
        var config = configuration.SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile(file =>
            {
                file.Path = "/appsettings.json";
                file.Optional = false;
                file.ReloadOnChange = true;
            }).Build();
        _connectionString = config.GetConnectionString("MySql");
    }

    // 执行一个查询并返回DataTable
    public static List<T> ExecuteQuery<T>(string query, params MySqlParameter[] parameters) where T : class, new()
    {
        using (var connection = new MySqlConnection(_connectionString))
        {
            connection.Open();
            using (var command = new MySqlCommand(query, connection))
            {
                command.Parameters.AddRange(parameters);
                using (var reader = command.ExecuteReader())
                {
                    var table = new DataTable();
                    table.Load(reader);
                    return ToList<T>(table);
                }
            }
        }
    }

    // 执行一个命令并返回影响的行数
    public static int ExecuteCommand(string commandText, params MySqlParameter[] parameters)
    {
        using (var connection = new MySqlConnection(_connectionString))
        {
            connection.Open();
            using (var command = new MySqlCommand(commandText, connection))
            {
                command.Parameters.AddRange(parameters);
                return command.ExecuteNonQuery();
            }
        }
    }

    // 执行一个查询并返回单个值
    public static object ExecuteScalar(string query, params MySqlParameter[] parameters)
    {
        using (var connection = new MySqlConnection(_connectionString))
        {
            connection.Open();
            using (var command = new MySqlCommand(query, connection))
            {
                command.Parameters.AddRange(parameters);
                return command.ExecuteScalar();
            }
        }
    }

    // 执行一个存储过程并返回一个包含结果集的DataTable
    public static DataTable ExecuteStoredProcedure(string procedureName, params MySqlParameter[] parameters)
    {
        using (var connection = new MySqlConnection(_connectionString))
        {
            connection.Open();
            using (var command = new MySqlCommand(procedureName, connection) { CommandType = CommandType.StoredProcedure })
            {
                command.Parameters.AddRange(parameters);
                using (var reader = command.ExecuteReader())
                {
                    var table = new DataTable();
                    table.Load(reader);
                    return table;
                }
            }
        }
    }

    public static List<T> ToList<T>(DataTable dataTable) where T : class, new()
    {
        Type t = typeof(T);
        PropertyInfo[] propertys = t.GetProperties();
        List<T> list = new List<T>();
        string typeName = string.Empty;

        foreach (DataRow dr in dataTable.Rows)
        {
            T entity = new T();
            foreach (PropertyInfo pi in propertys)
            {
                typeName = pi.Name;
                if (dataTable.Columns.Contains(typeName))
                {
                    if (!pi.CanWrite) continue;
                    object value = dr[typeName];
                    if (value == DBNull.Value) continue;
                    if (pi.PropertyType == typeof(string))
                    {
                        pi.SetValue(entity, value.ToString(), null);
                    }
                    else if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(int?))
                    {
                        pi.SetValue(entity, int.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(DateTime?) || pi.PropertyType == typeof(DateTime))
                    {
                        pi.SetValue(entity, DateTime.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(float))
                    {
                        pi.SetValue(entity, float.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(double))
                    {
                        pi.SetValue(entity, double.Parse(value.ToString()), null);
                    }
                    else
                    {
                        pi.SetValue(entity, value, null);
                    }
                }
            }
            list.Add(entity);
        }
        return list;
    }
}